﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
//using System.Data.OracleClient;
using Oracle.DataAccess.Client;
using System.Configuration;
using System.Data;
using DataAccess;
using System.Collections;

namespace DataAccess
{
    public class DataAccessClient
    {
        private OracleConnection connection;
        private OracleTransaction OraTrans;
        private OracleCommand cmd;
        private OracleDataAdapter adap;

        public DataAccessClient(DATABASE_PROXY db)
        {
            string data_source = ConfigurationManager.AppSettings["ORACLE_" + db.ToString() + "_DB_URL"];
            string username = ConfigurationManager.AppSettings["ORACLE_" + db.ToString() + "_DB_USER"];
            string password = ConfigurationManager.AppSettings["ORACLE_" + db.ToString() + "_DB_PASS"];
            this.connection = new OracleConnection(String.Format("Data Source={0}; user id={1}; password={2};", data_source, username, password));
        }

        public DataAccessClient(string conn)
        {
            this.connection = new OracleConnection(conn);
        }

        public DB_RESULT Insert(AbstractProperty myObject, out string error_message)
        {
            try
            {
                cmd = this.connection.CreateCommand();
                if (this.connection.State == ConnectionState.Closed)
                {
                    this.connection.Open();
                }
                OraTrans = this.connection.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = OraTrans;
                cmd.CommandType = System.Data.CommandType.Text;

                Type myType = myObject.GetType();
                IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());

                string insert_cmd = "insert into " + myType.Name.Substring(2);
                string insert_column = " (";
                string insert_value = " values (";
                foreach (PropertyInfo prop in props)
                {
                    if (prop.GetValue(myObject, null) != null)
                    {
                        insert_column += prop.Name + ",";
                        insert_value += ":" + prop.Name + ",";
                        cmd.Parameters.Add(":" + prop.Name, prop.GetValue(myObject, null));
                    }
                }
                insert_column = insert_column.Substring(0, insert_column.Length - 1) + ")";
                insert_value = insert_value.Substring(0, insert_value.Length - 1) + ")";

                cmd.CommandText = insert_cmd + insert_column + insert_value;
                cmd.ExecuteNonQuery();

                OraTrans.Commit();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = null;
                return DB_RESULT.COMPLETED;
            }
            catch (System.Exception ex)
            {
                OraTrans.Rollback();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = ex.Message;
                return DB_RESULT.INCOMPLETED;
            }
        }

        public DB_RESULT Update(AbstractProperty myObject, out string error_message)
        {
            try
            {
                cmd = this.connection.CreateCommand();
                if (this.connection.State == ConnectionState.Closed)
                {
                    this.connection.Open();
                }
                OraTrans = this.connection.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = OraTrans;
                cmd.CommandType = System.Data.CommandType.Text;

                Type myType = myObject.GetType();
                IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());

                string update_cmd = "update " + myType.Name.Substring(2);
                string update_column = " set ";
                foreach (PropertyInfo prop in props)
                {
                    if (prop.GetValue(myObject, null) != null)
                    {
                        update_column += prop.Name + " = :" + prop.Name + ",";
                        cmd.Parameters.Add(prop.Name, prop.GetValue(myObject, null));
                    }
                }
                update_column = update_column.Substring(0, update_column.Length - 1);

                string update_criteria = "";
                if (myObject.getStringCriteria() != "")
                {
                    update_criteria = " where " + myObject.getStringCriteria();
                }
                else
                {
                    foreach (Hashtable has_data in myObject.getHashtableCriteria())
                    {
                        foreach (string key in has_data.Keys)
                        {
                            update_criteria += " " + myObject.getOperator() + " " + key;
                            string[] arr = has_data[key].ToString().Split('๐');
                            cmd.Parameters.Add(arr[0], arr[1]);
                        }
                    }
                }

                if (update_criteria != "")
                {
                    update_criteria = " where " + update_criteria.Substring((myObject.getOperator() == "AND") ? 4 : 3);
                }

                cmd.CommandText = update_cmd + update_column + update_criteria;
                cmd.ExecuteNonQuery();

                OraTrans.Commit();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = null;
                return DB_RESULT.COMPLETED;
            }
            catch (System.Exception ex)
            {
                OraTrans.Rollback();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = ex.Message;
                return DB_RESULT.INCOMPLETED;
            }
        }

        public DB_RESULT Delete(AbstractProperty myObject, out string error_message)
        {
            try
            {
                cmd = this.connection.CreateCommand();
                if (this.connection.State == ConnectionState.Closed)
                {
                    this.connection.Open();
                }
                OraTrans = this.connection.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = OraTrans;
                cmd.CommandType = System.Data.CommandType.Text;

                Type myType = myObject.GetType();
                IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());

                string delete_cmd = "delete " + myType.Name.Substring(2);

                string delete_criteria = "";
                if (myObject.getStringCriteria() != "")
                {
                    delete_criteria = " where " + myObject.getStringCriteria();
                }
                else
                {
                    foreach (Hashtable has_data in myObject.getHashtableCriteria())
                    {
                        foreach (string key in has_data.Keys)
                        {
                            delete_criteria += " " + myObject.getOperator() + " " + key;
                            string[] arr = has_data[key].ToString().Split('๐');
                            cmd.Parameters.Add(arr[0], arr[1]);
                        }
                    }
                }

                if (delete_criteria != "")
                {
                    delete_criteria = " where " + delete_criteria.Substring((myObject.getOperator() == "AND") ? 4 : 3); ;
                }

                cmd.CommandText = delete_cmd + delete_criteria;

                cmd.ExecuteNonQuery();
                OraTrans.Commit();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = null;
                return DB_RESULT.COMPLETED;
            }
            catch (System.Exception ex)
            {
                OraTrans.Rollback();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = ex.Message;
                return DB_RESULT.INCOMPLETED;
            }
        }

        public DB_RESULT ExecuteNonQuery(string statement, Hashtable parameters, out int rows_affected, out string error_message)
        {
            try
            {
                cmd = this.connection.CreateCommand();
                if (this.connection.State == ConnectionState.Closed)
                {
                    this.connection.Open();
                }
                OraTrans = this.connection.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = OraTrans;

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = statement;
                if (parameters != null)
                {
                    foreach (string key in parameters.Keys)
                    {
                        cmd.Parameters.Add(key, parameters[key]);
                    }
                }
                rows_affected = cmd.ExecuteNonQuery();
                OraTrans.Commit();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = null;
                return DB_RESULT.COMPLETED;
            }
            catch (System.Exception ex)
            {
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                OraTrans.Rollback();
                rows_affected = 0;
                error_message = ex.Message;
                return DB_RESULT.INCOMPLETED;
            }
        }

        public DB_RESULT ExecuteScalar(string statement, Hashtable parameters, out object return_data, out string error_message)
        {
            try
            {
                cmd = this.connection.CreateCommand();
                if (this.connection.State == ConnectionState.Closed)
                {
                    this.connection.Open();
                }
                OraTrans = this.connection.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = OraTrans;

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = statement;

                if (parameters != null)
                {
                    foreach (string key in parameters.Keys)
                    {
                        cmd.Parameters.Add(key, parameters[key]);
                    }
                }

                return_data = cmd.ExecuteScalar();
                OraTrans.Commit();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = null;
                return DB_RESULT.COMPLETED;
            }
            catch (System.Exception ex)
            {
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                OraTrans.Rollback();
                return_data = null;
                error_message = ex.Message;
                return DB_RESULT.INCOMPLETED;
            }
        }

        public DB_RESULT ExecuteAdaptor(string statement, Hashtable parameters, out DataTable dt, out string error_message)
        {
            try
            {
                cmd = this.connection.CreateCommand();
                if (this.connection.State == ConnectionState.Closed)
                {
                    this.connection.Open();
                }
                OraTrans = this.connection.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = OraTrans;

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = statement;
                if (parameters != null)
                {
                    foreach (string key in parameters.Keys)
                    {
                        cmd.Parameters.Add(key, parameters[key]);
                    }
                }

                cmd.ExecuteScalar();
                adap = new OracleDataAdapter(cmd);
                dt = new DataTable();
                adap.Fill(dt);

                OraTrans.Commit();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = null;
                return DB_RESULT.COMPLETED;
            }
            catch (System.Exception ex)
            {
                dt = null;
                OraTrans.Rollback();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = ex.Message;
                return DB_RESULT.INCOMPLETED;
            }
        }

        public DB_RESULT ExecuteStoredProcedure(string name, Hashtable parameters, out DataTable dt, out string error_message)
        {
            try
            {
                cmd = this.connection.CreateCommand();
                if (this.connection.State == ConnectionState.Closed)
                {
                    this.connection.Open();
                }
                OraTrans = this.connection.BeginTransaction(IsolationLevel.ReadCommitted);
                cmd.Transaction = OraTrans;

                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = name;
                if (parameters != null)
                {
                    foreach (string key in parameters)
                    {
                        cmd.Parameters.Add(key, parameters[key]);
                    }
                }

                adap = new OracleDataAdapter(cmd);
                dt = new DataTable();
                adap.Fill(dt);

                OraTrans.Commit();

                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = null;
                return DB_RESULT.COMPLETED;
            }
            catch (System.Exception ex)
            {
                dt = null;
                OraTrans.Rollback();
                if (this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
                error_message = ex.Message;
                return DB_RESULT.INCOMPLETED;
            }
        }

        public IEnumerable<T> SelectDataToList<T>(out string error_message) where T : new()
        {
            try
            {
                IEnumerable<T> lObj = new List<T>();

                Type myType = typeof(T);
                IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());
                DataTable dt = new DataTable();
                string error;
                if (this.ExecuteAdaptor("select * from " + myType.Name.Substring(2), null, out dt, out error) == DB_RESULT.COMPLETED)
                {
                    if (dt != null)
                    {
                        foreach (DataRow dr in dt.Select())
                        {
                            T temp_obj = default(T);
                            temp_obj = Activator.CreateInstance<T>();
                            foreach (PropertyInfo prop in temp_obj.GetType().GetProperties())
                            {
                                if (dr[prop.Name] != DBNull.Value)
                                {
                                    prop.SetValue(temp_obj, dr[prop.Name], null);
                                }
                            }
                            lObj.ToList().Add(temp_obj);
                        }
                        error_message = null;
                        return lObj.AsParallel();
                    }
                    else
                    {
                        error_message = error;
                        return null;
                    }
                }
                else
                {
                    error_message = error;
                    return null;
                }
            }
            catch (System.Exception ex)
            {
                error_message = ex.Message;
                return null;
            }
        }

        public DataTable SelectDataToDataTable<T>(out string error_message)
        {
            try
            {
                List<T> lObj = new List<T>();

                Type myType = typeof(T);
                IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());
                DataTable dt = new DataTable();
                string error;
                if (this.ExecuteAdaptor("select * from " + myType.Name.Substring(2), null, out dt, out error) == DB_RESULT.COMPLETED)
                {
                    if (dt != null)
                    {
                        error_message = null;
                        return dt;
                    }
                    else
                    {
                        error_message = error;
                        return null;
                    }
                }
                else
                {
                    error_message = error;
                    return null;
                }
            }
            catch (System.Exception ex)
            {
                error_message = ex.Message;
                return null;
            }
        }
    }
}
